package com.seeyon.apps.brwh.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.seeyon.apps.brwh.kit.*;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.cap4.form.bean.FormTableBean;
import com.seeyon.cap4.form.modules.engin.manager.CapFormDefinitionManager;
import com.seeyon.cap4.form.po.CAPFormDefinition;
import com.seeyon.cap4.form.service.CAP4FormManager;
import com.seeyon.cap4.template.manager.CAPDataListSqlHelpManager;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.ctpenum.dao.cache.CtpEnumItemCache;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.common.log.CtpLogFactory;
import com.seeyon.ctp.common.po.content.CtpContentAll;
import com.seeyon.ctp.common.po.ctpenumnew.CtpEnumItem;
import com.seeyon.ctp.common.po.template.CtpTemplate;
import com.seeyon.ctp.common.template.manager.TemplateManager;
import com.seeyon.ctp.organization.bo.V3xOrgMember;
import com.seeyon.ctp.organization.manager.OrgManager;

import org.apache.commons.logging.Log;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * SRM看板逻辑处理
 */
public class SRMKanBanDataIPDService {
    private static final Log LOG = CtpLogFactory.getLog(SRMKanBanDataIPDService.class);
    private static final String gysdaTempCode = AppContext.getSystemProperty("brwh.gysdaTemp");
    private static final String cgxqddTemp = AppContext.getSystemProperty("brwh.cgxqddTemp");
    private static final String gysxzTempCode = AppContext.getSystemProperty("brwh.gysxzTemp");
    private static final String gysjxpgTempCode = AppContext.getSystemProperty("brwh.gysjxpgTemp");
    private static final String gysrzsqTempCode = AppContext.getSystemProperty("brwh.gysrzsqTemp");
    private static final String gyskfTempCode = AppContext.getSystemProperty("brwh.gyskfTemp");
    private static final String kbqsHtDabTempCode = AppContext.getSystemProperty("brwh.kbqsHtDabTemp");
    private static final String cgddLcTempCode = AppContext.getSystemProperty("brwh.cgddLcTemp");
    private static final String cgrkTempCode = AppContext.getSystemProperty("brwh.cgrkTemp");
    private static final String sptpdjTempCode = AppContext.getSystemProperty("brwh.sptpdjTemp");
    private static final String cgfkTempCode = AppContext.getSystemProperty("brwh.cgfkTemp");
    private static final String gysxzDaTempCode = AppContext.getSystemProperty("brwh.gysxzDaTemp");
    private static final String cghtddTempCode = AppContext.getSystemProperty("brwh.cghtddTemp");
    private static final String cgdddaTempCode = AppContext.getSystemProperty("brwh.cgdddaFormCode");
    private static final String cgfkDATempCode = AppContext.getSystemProperty("brwh.cgfkDATemp");
    private static final String spdjDATempCode = AppContext.getSystemProperty("brwh.spdjDATemp");
//    private static final String cgxqddSfyqz = AppContext.getSystemProperty("brwh.cgxqdd_sfyqz");
//    private static final String cgddddZbSfyq = AppContext.getSystemProperty("brwh.cgdddd_zb_sfyq");
//    private static final String cghtddSfyq = AppContext.getSystemProperty("brwh.cghtdd_sfyq");

    private static final String cgrkDATempCode = AppContext.getSystemProperty("brwh.cgrkDATemp");
    private CAP4FormManager cap4FormManager = (CAP4FormManager) AppContext.getBean("cap4FormManager");
    private CtpEnumItemCache ctpEnumItemCache = (CtpEnumItemCache)AppContext.getBean("ctpEnumItemCache");

    private static OrgManager orgManager = (OrgManager) AppContext.getBean("orgManager");
    private CAPDataListSqlHelpManager capDataListSqlHelpManager = (CAPDataListSqlHelpManager) AppContext.getBean("capDataListSqlHelpManager");
    private CapFormDefinitionManager capFormDefinitionManager = (CapFormDefinitionManager)AppContext.getBean("capFormDefinitionManager");



    /**
     * 获取看板地图数据逻辑
     * @return
     */
    public List<Map<String,Object>> getDiTuDataList(){
        ArrayList<Map<String, Object>> resultMapList = new ArrayList<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(gysdaTempCode);
        } catch (BusinessException e) {
            LOG.info("根据供应商档案模板编码获取供应商表信息异常，供应商档案模板："+gysdaTempCode+"：",e);
            return resultMapList;
        }
        FormTableBean masterTableBean = formBean.getMasterTableBean();
        String qzEnumCode = "";
        String zsEnumCode = "";
        String zyEnumCode = "";
        //获取枚举数据
        try {
            //潜在
            qzEnumCode =  FormCap4Kit.getEnumByNameList("gysdj_qz").get(0).getId().toString();
            //正式
            zsEnumCode = FormCap4Kit.getEnumByNameList("gysdj_zs").get(0).getId().toString();
            //启用
            zyEnumCode = FormCap4Kit.getEnumByNameList("gyszt_qy").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("获取SRM看板枚举Id异常：",e);
            return resultMapList;
        }

        //获取字段信息
        FormFieldBean gysztFieldBean = formBean.getFieldBeanByDisplay("供应商状态");
        FormFieldBean gysdjFieldBean = formBean.getFieldBeanByDisplay("供应商等级");
        FormFieldBean szcsFieldBean = formBean.getFieldBeanByDisplay("所在城市");


        String tableName = masterTableBean.getTableName();
        //获取供应商档案数据
        StringBuilder sql = new StringBuilder("select ");
        sql.append(gysdjFieldBean.getName() + " dj,");
        sql.append(gysztFieldBean.getName() + " zt,");
        sql.append(szcsFieldBean.getName() + " cs");
        sql.append(" from "+tableName + " where ");
        sql.append(gysztFieldBean.getName() +" = '"+zyEnumCode+"' and ");
        sql.append(gysdjFieldBean.getName() + " in ('"+zsEnumCode+"','"+qzEnumCode+"') and ");
        sql.append(szcsFieldBean.getName() + " is not null");
        List<Map<String, Object>> maps = null;
        try {
            maps = DBKit.excuteSQL(sql.toString(), null);
        } catch (Exception e) {
            LOG.info("获取STM看板数据异常：",e);
            return resultMapList;
        }
        //根据城市分组
        Map<String, List<Map<String, Object>>> groupDataMap = maps.stream().collect(Collectors.groupingBy(item -> item.get("cs").toString()));
        for (String s : groupDataMap.keySet()) {
            List<Map<String, Object>> maps1 = groupDataMap.get(s);
            String finalZsEnumCode = zsEnumCode;
            String qzGysCode = qzEnumCode;
            //获取正式供应商
            List<Map<String, Object>> zsgysList = maps1.stream().filter(m -> StrKit.str(m.get("dj")).equals(finalZsEnumCode)).collect(Collectors.toList());
            List<Map<String, Object>> qzgysList = maps1.stream().filter(m -> StrKit.str(m.get("dj")).equals(qzGysCode)).collect(Collectors.toList());
            //筛选全国的正式供应商数据
            List<Map<String, Object>> countList = maps.stream().filter(m -> StrKit.str(m.get("dj")).equals(finalZsEnumCode)).collect(Collectors.toList());

            int count = countList.size();
            int zsgysCount = zsgysList.size();
            //当前正式供应商除以总共的正式供应商
            String divide = MyMathUtil.dividePercent((double) zsgysCount, (double) count, "#0.00");
            HashMap<String, Object> map = new HashMap<>();
            map.put("sumCount",maps1.size());
            map.put("quName",s);
            map.put("qzCount",qzgysList.size());
            map.put("zsCount",zsgysCount);
            map.put("zsPercent",divide);
            resultMapList.add(map);
        }
        return resultMapList;
    }


    /**
     * 获取详细数据列表
     * @param quName
     * @param type  1 正式   2 潜在  0 所有
     * @return
     */
    public Map<String,Object> getDataList(String quName,String type){
        HashMap<String, Object> resultMap = new HashMap<>();
        ArrayList<Map<String, Object>> resultMapList = new ArrayList<>();
        String titele = "供应商编码-gysCode,供应商名称-gysName,供应商等级-gysDj,供应商类型-gysLx,供应商状态-gysZt,是否考察-gysSfkc,所在城市-gysCs,联系人-gyslxr,联系人职务-gysLxrZw,联系人电话-gysLxrDh,详情-url";
        List<Map<String,Object>> titleList = new ArrayList<>();
        for (String s : titele.split(",")) {
            String[] split = s.split("-");
            HashMap<String, Object> map = new HashMap<>();
            map.put("name",split[0]);
            map.put("key",split[1]);
            titleList.add(map);
        }
        //获取表头信息
        resultMap.put("titleList",titleList);
        resultMap.put("dataList",resultMapList);

        //根据省，和查询类型，获取数据列表信息
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(gysdaTempCode);
        } catch (BusinessException e) {
            LOG.info("根据供应商档案模板编码获取供应商表信息异常，供应商档案模板："+gysdaTempCode+"：",e);
            return resultMap;
        }
        FormTableBean masterTableBean = formBean.getMasterTableBean();
        String qzEnumCode = "";
        String zsEnumCode = "";
        String zyEnumCode = "";
        //获取枚举数据
        try {
            //潜在
            qzEnumCode =  FormCap4Kit.getEnumByNameList("gysdj_qz").get(0).getId().toString();
            //正式
            zsEnumCode = FormCap4Kit.getEnumByNameList("gysdj_zs").get(0).getId().toString();
            //启用
            zyEnumCode = FormCap4Kit.getEnumByNameList("gyszt_qy").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("获取SRM看板枚举Id异常：",e);
            return resultMap;
        }
        //获取字段信息
        //供应商编码
        FormFieldBean gysbmFieldBean = formBean.getFieldBeanByDisplay("供应商编码");
        FormFieldBean gysmcFieldBean = formBean.getFieldBeanByDisplay("供应商名称");
        FormFieldBean gysdjFieldBean = formBean.getFieldBeanByDisplay("供应商等级");
        FormFieldBean gyslxFieldBean = formBean.getFieldBeanByDisplay("供应商类型");
        FormFieldBean gysztFieldBean = formBean.getFieldBeanByDisplay("供应商状态");
        FormFieldBean sfkcFieldBean = formBean.getFieldBeanByDisplay("是否考察");
        FormFieldBean szcsFieldBean = formBean.getFieldBeanByDisplay("所在城市");
        FormFieldBean lxrFieldBean = formBean.getFieldBeanByDisplay("联系人");
        FormFieldBean lxrzwFieldBean = formBean.getFieldBeanByDisplay("联系人职务");
        FormFieldBean lxrdhFieldBean = formBean.getFieldBeanByDisplay("联系电话");

        String tableName = masterTableBean.getTableName();
        //获取供应商档案数据
        StringBuilder sql = new StringBuilder("select f.id,");
        sql.append(gysbmFieldBean.getName()).append(" gyscode,");
        sql.append(gysmcFieldBean.getName()).append(" gysname,");
        sql.append(gysdjFieldBean.getName()).append(" gysdj,");
        sql.append("ct.SHOWVALUE gysdjName,");
        sql.append(gyslxFieldBean.getName()).append(" gyslx,");
        sql.append("ct1.SHOWVALUE gyslxName,");
        sql.append(gysztFieldBean.getName()).append(" gyszt,");
        sql.append("ct2.SHOWVALUE gysztName,");
        sql.append(sfkcFieldBean.getName()).append(" gyssfkc,");
        sql.append("ct3.SHOWVALUE gyssfkcName,");
        sql.append(szcsFieldBean.getName()).append(" gyscs,");
        sql.append(lxrFieldBean.getName()).append(" gyslxr,");
        sql.append(lxrzwFieldBean.getName()).append(" gyslxrzw,");
        sql.append(lxrdhFieldBean.getName()).append(" gyslxrdh");
        sql.append(" from ").append(tableName).append(" f");
        sql.append(" left join CTP_ENUM_ITEM ct on ct.ID = ").append("f.").append(gysdjFieldBean.getName());
        sql.append(" left join CTP_ENUM_ITEM ct1 on ct1.ID = ").append("f.").append(gyslxFieldBean.getName());
        sql.append(" left join CTP_ENUM_ITEM ct2 on ct2.ID = ").append("f.").append(gysztFieldBean.getName());
        sql.append(" left join CTP_ENUM_ITEM ct3 on ct3.ID = ").append("f.").append(sfkcFieldBean.getName());
        sql.append(" where ");
        sql.append(gysztFieldBean.getName()).append(" = '").append(zyEnumCode).append("' and ");
        sql.append(szcsFieldBean.getName()).append(" = '").append(quName).append("' and ");
        if(type.equals("1")){
            //正式
            sql.append(gysdjFieldBean.getName()).append(" = '").append(zsEnumCode).append("'");
        }else if(type.equals("2")){
            //潜在
            sql.append(gysdjFieldBean.getName()).append(" = '").append(qzEnumCode).append("'");
        }else{
            //所有
            sql.append(gysdjFieldBean.getName()).append(" in ('").append(zsEnumCode).append("','").append(qzEnumCode).append("')");
        }
        List<Map<String, Object>> maps = null;
        try {
            maps = DBKit.excuteSQL(sql.toString(), null);
        } catch (Exception e) {
            LOG.info("获取STM看板数据异常：",e);
            return resultMap;
        }
        for (Map<String, Object> map : maps) {
            String url = "";
            try {
                CAPFormDefinition capFormDefinition = capFormDefinitionManager.selectById(formBean.getId());
                String appbindInfo = capFormDefinition.getAppbindInfo();
                Map<String,Object> jsonObject = JSONObject.parseObject(appbindInfo,Map.class);
                List<Map<String, Object>> formBindAuthList =  (List<Map<String, Object>>)jsonObject.get("formBindAuthList");
                String tempId = StrKit.str(formBindAuthList.get(0).get("id"));
                url = "/seeyon/cap4/businessTemplateController.do?method=formContent&type=browse&moduleId="+map.get("id").toString()+"&formTemplateId="+tempId+"&moduleType=42";
            } catch (BusinessException e) {
                LOG.info("获取档案表跳转地址异常：",e);
            }
            HashMap<String, Object> dataMap = new HashMap<>();
            dataMap.put("gysCode",map.get("gyscode") == null?"":map.get("gyscode"));
            dataMap.put("gysName",map.get("gysname") == null?"":map.get("gysname"));
            dataMap.put("gysDj",map.get("gysdj") == null?"":map.get("gysdjname"));
            dataMap.put("gysLx",map.get("gyslx") == null?"":map.get("gyslxname"));
            dataMap.put("gysZt",map.get("gyszt") == null?"":map.get("gysztname"));
            dataMap.put("gysSfkc",map.get("gyssfkc") == null ? "":map.get("gyssfkcname"));
            dataMap.put("gysCs",map.get("gyscs") == null ? "":map.get("gyscs"));
            dataMap.put("gyslxr",map.get("gyslxr") == null?"":map.get("gyslxr"));
            dataMap.put("gysLxrZw",map.get("gyslxrzw") == null?"":map.get("gyslxrzw"));
            dataMap.put("gysLxrDh",map.get("gyslxrdh") == null?"":map.get("gyslxrdh"));
            dataMap.put("url",url);
            resultMapList.add(dataMap);
        }
        return resultMap;
    }


    /**
     * 获取地图上更多按钮跳转地址
     * @return
     */
    public Map<String,Object> getMoreUrl(){
        String title = "供应商档案SRM";
        String bbAddres = FormCap4Kit.getBbAddres(title);
        HashMap<String, Object> map = new HashMap<>();
        map.put("url",bbAddres);
        return map;
    }

    /**
     * 获取采购工程师工作状态数据
     * @return
     */
    public ArrayList<Map<String, Object>> getCggcsgzztData() throws BusinessException, SQLException {
        ArrayList<Map<String, Object>> maps = new ArrayList<>();
        //获取供应商开发流程数据
        Map<String, List<Map<String, Object>>> gyskflcDataList = getFlowStatementInfo(gyskfTempCode);
        //获取供应商认证申请流程数据
        Map<String, List<Map<String, Object>>> gyszrlcDataList = getFlowStatementInfo(gysrzsqTempCode);
        //获取供应商绩效评估数据
        Map<String, List<Map<String, Object>>> gysjxpgDataList = getFlowStatementInfo(gysjxpgTempCode);
        //获取供应商选择流程
        Map<String, List<Map<String, Object>>> gysxzlcDataList = getFlowStatementInfo(gysxzTempCode);
        //获取采购需求档案表数据
        Map<String, List<Map<String, Object>>> cgxqdaDataList = getCgxqDataInfo(cgxqddTemp);

        //组装人员信息
        ArrayList<String> userNameList = new ArrayList<>();
        for (String s : gyskflcDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : gyszrlcDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : gysjxpgDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : gysxzlcDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : cgxqdaDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        Map<String, Object> jblMapData = getJblData();

        for (String s : userNameList) {
            List<Map<String, Object>> gyskfList = gyskflcDataList.get(s);
            List<Map<String, Object>> gyszrlcList = gyszrlcDataList.get(s);
            List<Map<String, Object>> gysjxpgList = gysjxpgDataList.get(s);
            List<Map<String, Object>> gysxzlcList = gysxzlcDataList.get(s);
            List<Map<String, Object>> cgxqList = cgxqdaDataList.get(s);


            HashMap<String, Object> map = new HashMap<>();
            map.put("userName",s);
            map.put("gyskf",gyskfList == null?0:gyskfList.size());
            map.put("gyskfUrl",FormCap4Kit.getBbAddres("供应商开发需求"));
            map.put("gyszrlc",gyszrlcList == null?0:gyszrlcList.size());
            map.put("gyszrlcUrl",FormCap4Kit.getBbAddres("供应商认证"));
            map.put("gysjxpg",gysjxpgList == null?0:gysjxpgList.size());
            map.put("gysjxpgUrl",FormCap4Kit.getBbAddres("供应商绩效评估"));
            map.put("gysxzlc",gysxzlcList == null?0:gysxzlcList.size());
            map.put("gysxzlcUrl",FormCap4Kit.getBbAddres("供应商选择"));
            map.put("cgxq",cgxqList == null?0:cgxqList.size());
            map.put("cgxqUrl",FormCap4Kit.getBbAddres("采购需求"));
            map.put("jbl",jblMapData.get(s) == null?0.0:Double.parseDouble(jblMapData.get(s).toString()));
            map.put("jblUrl",FormCap4Kit.getBbAddres("降本率统计"));
            jblMapData.remove(s);
            maps.add(map);
        }
        for (String s : jblMapData.keySet()){
            HashMap<String, Object> map = new HashMap<>();
            map.put("userName",s);
            map.put("gyskf",0);
            map.put("gyskfUrl",FormCap4Kit.getBbAddres("供应商开发需求"));
            map.put("gyszrlc",0);
            map.put("gyszrlcUrl",FormCap4Kit.getBbAddres("供应商认证"));
            map.put("gysjxpg",0);
            map.put("gysjxpgUrl",FormCap4Kit.getBbAddres("供应商绩效评估"));
            map.put("gysxzlc",0);
            map.put("gysxzlcUrl",FormCap4Kit.getBbAddres("供应商选择"));
            map.put("cgxq",0);
            map.put("cgxqUrl",FormCap4Kit.getBbAddres("采购需求"));
            map.put("jbl",jblMapData.get(s) == null?0.0:Double.parseDouble(jblMapData.get(s).toString()));
            map.put("jblUrl",FormCap4Kit.getBbAddres("降本率统计"));
            maps.add(map);
        }
        return maps;
    }

    /**
     * 总降本金额
     * @return
     */
    public Map<String, Object> getZjbjeMapData() throws BusinessException, SQLException {
        /* 从系统属性中获取配置信息 */
        //采购订单档案
        String cgdddaFormCode = AppContext.getSystemProperty("brwh.cgdddaFormCode");
        Map<Object, Object> selectQxList = selectNotQxList(cgdddaFormCode);
        List<Object> idsListObject = (List<Object>) selectQxList.get("ids");
        String commaSeparatedString = idsListObject.stream()
                .map(Object::toString)
                .collect(Collectors.joining(","));
        //明细表降本总额
        String cgdddasubjbze = AppContext.getSystemProperty("brwh.cgdddasubjbze");
        //物料名称
        String scwlmx = AppContext.getSystemProperty("brwh.scwlmx");

        //获取对应表单
        FormBean formBean = cap4FormManager.getFormByFormCode(cgdddaFormCode);
        List<FormFieldBean> allFieldBeans = formBean.getAllFieldBeans();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, formmain_id,");
        String cgdddasubjbzeFieldName = "";
        for (FormFieldBean formFieldBean : allFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (formFieldBean.isSubField()) {
                //降本总额
                if (cgdddasubjbze.equals(fieldDesc)) {
                    cgdddasubjbzeFieldName = formFieldBean.getName();
                }
                if (!"".equals(fieldDesc)) {
                    String fieldName = formFieldBean.getName();
                    stb.append(fieldName).append(",");
                }

            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        List<FormTableBean> subTableBean = formBean.getSubTableBean();
        Map<String, Object> resMap = new HashMap<>();
        StringBuilder newStr=new StringBuilder();
        for (int i = 0; i < subTableBean.size(); i++) {
            String tableName = "";
            if (scwlmx.equals(subTableBean.get(i).getDisplay())) {
                tableName = subTableBean.get(i).getTableName();
                if (!tableName.isEmpty()) {
                    newStr .append(str + " from " + tableName);
                    if (!commaSeparatedString.isEmpty()){
                        newStr .append(" where formmain_id in ("+commaSeparatedString+")");;
                    }
                    LOG.info("采购订单明细表-----sql语句：" + newStr);
                }
            }
        }
        double sum=0.0;
        /* 执行SQL查询 */
        List<Map<String, Object>> dataList = capDataListSqlHelpManager.selectDataList(newStr.toString(), null);
        LOG.info("采购订单档案信息-------" + JSON.toJSONString(dataList));
        if (!dataList.isEmpty()) {
            for (Map<String, Object> stringObjectMap : dataList) {
                sum+=Double.parseDouble(stringObjectMap.get(cgdddasubjbzeFieldName).toString());
            }
        }
        sum = (double) Math.round(sum * 100) / 100;
        double sumInTenThousand = sum / 10000;
        sumInTenThousand = (double) Math.round(sumInTenThousand * 100) / 100;
        resMap.put("sum",sumInTenThousand+"万");
        resMap.put("url",FormCap4Kit.getBbAddres("降本率统计"));
        return resMap;
    }



    /**
     * 获取采购专员工作状态数据
     * @return
     */
    public ArrayList<Map<String, Object>> getCgzygzztData(){
        ArrayList<Map<String, Object>> maps = new ArrayList<>();
        //获取看板取数-合同档案表数据
        Map<String, List<Map<String, Object>>> kbqsHtDabDataList = getKbqsHtDataInfo(kbqsHtDabTempCode);
        //获取采购订单流程数据
        Map<String, List<Map<String, Object>>> cgddLcDataList = getFlowStatementInfo(cgddLcTempCode);
        //获取采购入库流程数据
        Map<String, List<Map<String, Object>>> cgrkDataList = getFlowStatementInfo(cgrkTempCode);
        //获取收票登记流程数据
        Map<String, List<Map<String, Object>>> sptpdjDataList = getSpFlowStatementInfo(sptpdjTempCode);

        //获取采购付款流程数据
        Map<String, List<Map<String, Object>>> cgfkDataList = getFlowStatementInfo(cgfkTempCode);

        //组装人员信息
        ArrayList<String> userNameList = new ArrayList<>();
        for (String s : kbqsHtDabDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : cgddLcDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : cgrkDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : cgfkDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }
        for (String s : sptpdjDataList.keySet()) {
            if(!userNameList.contains(s)){
                userNameList.add(s);
            }
        }

        for (String s : userNameList) {
            List<Map<String, Object>> kbqsHtList = kbqsHtDabDataList.get(s);
            List<Map<String, Object>> cgddLcList = cgddLcDataList.get(s);
            List<Map<String, Object>> cgrkList = cgrkDataList.get(s);
            List<Map<String, Object>> cgfkList = cgfkDataList.get(s);
            List<Map<String, Object>> sptpdjList = sptpdjDataList.get(s);


            HashMap<String, Object> map = new HashMap<>();
            map.put("userName",s);
            map.put("kbqsHt",kbqsHtList == null?0:kbqsHtList.size());
            map.put("kbqsHtUrl",FormCap4Kit.getBbAddres("采购合同签订"));
            map.put("cgddLc",cgddLcList == null?0:cgddLcList.size());
            map.put("cgddLcUrl",FormCap4Kit.getBbAddres("采购订单"));
            map.put("cgrk",cgrkList == null?0:cgrkList.size());
            map.put("cgrkUrl",FormCap4Kit.getBbAddres("采购入库"));
            map.put("cgfk",cgfkList == null?0:cgfkList.size());
            map.put("cgfkUrl",FormCap4Kit.getBbAddres("收票登记"));
            map.put("sptpdj",sptpdjList == null?0:sptpdjList.size());
            map.put("sptpdjUrl",FormCap4Kit.getBbAddres("采购付款"));
            maps.add(map);
        }
        return maps;
    }

    /**
     * 获取采购可视化数据1
     * @return
     */
    public Map<String,Object> getCgkshGhzqData() throws Exception {
        //获取采购需求数据
        Map<String, Object> cgkshCgxqDataInfo = getCgkshCgxqDataInfo(cgxqddTemp);
        //供应商选择档案数据
        Map<String, Object> cgkshGysxzDataInfo = getCgkshGysxzDataInfo(gysxzDaTempCode);
        //采购合同档案数据
        Map<String, Object> cgkshCghtDataInfo = getCgkshCghtDataInfo(cghtddTempCode);
        //采购订单档案数据
        Map<String, Object> cgkshCgDDDataInfo = getCgkshCgDDDataInfo(cgdddaTempCode);
        //采购付款
        Map<String, Object> cgkshCgFkDataInfo = getCgkshCgFkDataInfo(cgdddaTempCode);
        //采购入库
        Map<String, Object> cgkshCgrkDataInfo = getCgkshCgrkDataInfo(cgdddaTempCode);
        //收票登记

        Map<String, Object> cgkshSpdjDataInfo = getCgkshSpdjDataInfo(cgdddaTempCode);

        //获取所有平均处理时间countMinute
        long xqFz = (long)cgkshCgxqDataInfo.get("countMinute");
        long gysxzFz = (long)cgkshGysxzDataInfo.get("countMinute");
        long chhtFz = (long)cgkshCghtDataInfo.get("countMinute");
        long chddFz = (long)cgkshCgDDDataInfo.get("countMinute");
        long cgfkFz = (long)cgkshCgFkDataInfo.get("countMinute");
        long cgrkFz = (long)cgkshCgrkDataInfo.get("countMinute");
//        long spdjFz = (long)cgkshSpdjDataInfo.get("countMinute");
        //总的处理分钟
//        long sumFz = xqFz+gysxzFz+chhtFz+chddFz+cgfkFz+cgrkFz+spdjFz;
        long sumFz = xqFz+gysxzFz+chhtFz+chddFz+cgfkFz+cgrkFz;
        //平均处理分钟
        int averageMinute = (int)Math.ceil((float)sumFz / 6);
        //int averageDay = (int)Math.ceil(averageMinute / 60 / 24.0);
        double averageDay = 0;
        String count=null;
        String sj=null;
        FormBean formBean=null;
        try {
            formBean = cap4FormManager.getFormByFormCode("CGXQDA_02");
            if (formBean!=null){
                String sfrk=formBean.getFieldBeanByDisplay("是否入库完成").getName();
                String sf =  FormCap4Kit.getEnumByNameList("Y").get(0).getId().toString();
                String pjsj = formBean.getFieldBeanByDisplay("平均处理时长").getName();
                String kssj = formBean.getFieldBeanByDisplay("最晚入库时间").getName();
                String tableName = formBean.getFieldBeanByDisplay("平均处理时长").getOwnerTableName();
                String sql="SELECT count(*) as pjsjcount ,SUM("+pjsj+")"+" AS pjsjsum FROM "+tableName
                        +" where "+sfrk+" = '"+sf+"'"
                        +" and "+kssj+" > '2025-3-5 15:30:00'";
                List<Map<String,Object>> list = capDataListSqlHelpManager.selectDataList(sql, null);
                if (!list.isEmpty()){
                    Map<String,Object> map=(Map<String,Object>)list.get(0);
                    String pjsjcount = StrKit.str(map.get("pjsjcount"));
                    String pjsjsum = StrKit.str(map.get("pjsjsum"));
                    if (pjsjcount.equals("0")|| pjsjsum.equals("")||pjsjcount.equals("") ){
                        averageDay=0.00;
                    }else {
                        Double douAverageDay = Double.parseDouble(pjsjsum) / Double.parseDouble(pjsjcount) ;
                        DecimalFormat df = new DecimalFormat("#.0");
                        count=pjsjcount;
                        sj=pjsjsum;
                        averageDay = Double.parseDouble(df.format(douAverageDay));
                    }
                }
            }
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数供应商选择档案表数据信息异常：",e);
        }

        HashMap<String, Object> ghDataMap = new HashMap<>();
        ghDataMap.put("cgxqData",cgkshCgxqDataInfo);
        ghDataMap.put("gysxzData",cgkshGysxzDataInfo);
        ghDataMap.put("cghtData",cgkshCghtDataInfo);
        ghDataMap.put("cgddData",cgkshCgDDDataInfo);
        ghDataMap.put("cgfkData",cgkshCgFkDataInfo);
        ghDataMap.put("cgrkData",cgkshCgrkDataInfo);
        ghDataMap.put("spdjData",cgkshSpdjDataInfo);

        HashMap<String, Object> map = new HashMap<>();
        map.put("averageDay",averageDay);
        map.put("ghData",ghDataMap);
        map.put("count",count);
        map.put("sj",sj);
        return map;
    }

    /**
     * 获取采购可视化数据测试
     * @return
     */
    public Map<String,Object> getCgkshGhzqDataTest(){
        //获取采购需求数据
        Map<String, Object> cgkshCgxqDataInfo = getCgkshCgxqDataInfo(cgxqddTemp);
        //采购合同档案数据
        Map<String, Object> cgkshCghtDataInfo = getCgkshCghtDataInfo(cghtddTempCode);
        //采购订单档案数据
        Map<String, Object> cgkshCgDDDataInfo = getCgkshCgDDDataInfo(cgdddaTempCode);

        //获取所有平均处理时间countMinute
        long xqFz = (long)cgkshCgxqDataInfo.get("countMinute");
        long chhtFz = (long)cgkshCghtDataInfo.get("countMinute");
        long chddFz = (long)cgkshCgDDDataInfo.get("countMinute");
        //总的处理分钟
        long sumFz = xqFz+chhtFz+chddFz;
        //平均处理分钟
        int averageMinute = (int)Math.ceil((float)sumFz / 3);
        int averageDay = (int)Math.ceil(averageMinute / 60 / 24.0);

        HashMap<String, Object> ghDataMap = new HashMap<>();
        ghDataMap.put("cgxqData",cgkshCgxqDataInfo);
        ghDataMap.put("cghtData",cgkshCghtDataInfo);
        ghDataMap.put("cgddData",cgkshCgDDDataInfo);
        HashMap<String, Object> map = new HashMap<>();
        map.put("averageDay",averageDay);
        map.put("ghData",ghDataMap);
        return map;
    }



    /**
     * 获取采购可视化更多列表跳转地址
     * @return
     */
    public Map<String,Object> gerCgkshGdDataListUrl(){
        HashMap<String, Object> resultMap = new HashMap<>();
        String url = "";
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(cgxqddTemp);
        } catch (BusinessException e) {
            LOG.info("根据采购需求档案模板编码获取采购需求档案表信息异常，采购需求档案模板："+cgxqddTemp+"：",e);
            return resultMap;
        }

        try {
            CAPFormDefinition capFormDefinition = capFormDefinitionManager.selectById(formBean.getId());
            String appbindInfo = capFormDefinition.getAppbindInfo();
            Map<String,Object> jsonObject = JSONObject.parseObject(appbindInfo,Map.class);
            List<Map<String, Object>> formBindAuthList =  (List<Map<String, Object>>)jsonObject.get("formBindAuthList");
            String tempId = StrKit.str(formBindAuthList.get(0).get("id"));
            url = "/seeyon/cap4/businessTemplateController.do?method=capUnflowList&srcFrom=bizconfig&moduleId="+tempId+"&formId="+formBean.getId();
        } catch (BusinessException e) {
            LOG.info("获取采购需求档案列表跳转地址异常：",e);
        }
        resultMap.put("url",url);
        return resultMap;
    }



    /**
     * 获取采购可视化采购需求物料进度
     * @return
     */
    public List<Map<String,Object>> getCgxqWlJdData(){
        ArrayList<Map<String, Object>> resultMaps = new ArrayList<>();
        //获取采购需求，物料信息
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(cgxqddTemp);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购需求档案表数据信息异常：",e);
            return resultMaps;
        }

        //获取采购合同数据信息
        FormBean htDaFormBean = null;
        try {
            htDaFormBean = cap4FormManager.getFormByFormCode(cghtddTempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购合同档案表数据信息异常：",e);
            return resultMaps;
        }

        //获取采购订单数据信息
        FormBean cgddFormBean = null;
        try {
            cgddFormBean = cap4FormManager.getFormByFormCode(cgdddaTempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购订单档案表数据信息异常：",e);
            return resultMaps;
        }

        //获取需求主表字段
        FormFieldBean wlNameFieldBean = formBean.getFieldBeanByDisplay("物料名称");
        FormFieldBean wlCodeFieldBean = formBean.getFieldBeanByDisplay("物料编号");
        FormFieldBean sfxjbjFieldBean = formBean.getFieldBeanByDisplay("是否已询报价");

        //采购合同档案主表字段
        FormFieldBean htbhFieldBean = htDaFormBean.getFieldBeanByDisplay("合同编号");

        //获取采购订单主表字段
        FormFieldBean cgddDjbhFieldBean = cgddFormBean.getFieldBeanByDisplay("单据编号");

        //获取采购需求-物料明细数据字段
        FormFieldBean cgxqdhFieldBean = formBean.getFieldBeanByDisplay("采购需求单号-物料明细单");
        FormFieldBean xsxmmcFieldBean = formBean.getFieldBeanByDisplay("销售项目名称-物料明细单");
        FormFieldBean xsxmbmFieldBean = formBean.getFieldBeanByDisplay("销售项目编码-物料明细单");
        FormFieldBean xsddbhFieldBean = formBean.getFieldBeanByDisplay("销售订单编号-物料明细单");
        FormFieldBean gisxzlcztFieldBean = formBean.getFieldBeanByDisplay("供应商选择流程状态-物料");
        FormFieldBean htztFieldBean = formBean.getFieldBeanByDisplay("合同状态-物料");
        FormFieldBean ddztFieldBean = formBean.getFieldBeanByDisplay("订单状态-物料");

        //获取采购合同档案-采购订单信息明细字段
        FormFieldBean htXsddbhFieldBean = htDaFormBean.getFieldBeanByDisplay("销售订单编号-采购订单明细");
        FormFieldBean htCgxqdhFieldBean = htDaFormBean.getFieldBeanByDisplay("采购需求单号-采购订单明细");

        //采购订单档案-物料明细字段
        FormFieldBean cgddCgxqsqdhFieldBean = cgddFormBean.getFieldBeanByDisplay("采购需求申请单号-物料明细");
        FormFieldBean cgddXsddbhFieldBean = cgddFormBean.getFieldBeanByDisplay("销售订单编号-物料明细");
        FormFieldBean rkztFieldBean = cgddFormBean.getFieldBeanByDisplay("入库状态-物料明细");
        FormFieldBean fkztFieldBean = cgddFormBean.getFieldBeanByDisplay("付款状态-物料明细");
        FormFieldBean spztFieldBean = cgddFormBean.getFieldBeanByDisplay("收票状态-物料明细");

        //采购需求主表名称
        String cgxqMasterTableName = wlNameFieldBean.getOwnerTableName();
        //采购需求明细表名称
        String cgxqSubTableName = cgxqdhFieldBean.getOwnerTableName();
        //采购合同档案主表名称
        String cghtMasterTableName = htbhFieldBean.getOwnerTableName();
        //采购合同档案明细表名称
        String cghtSubTableName = htXsddbhFieldBean.getOwnerTableName();
        //采购订单档案主表名称
        String cgdddaMsterTableName = cgddDjbhFieldBean.getOwnerTableName();
        //采购订单档案明细表名称
        String cgdddaSubTableName = cgddCgxqsqdhFieldBean.getOwnerTableName();

        //获取采购需求数据
        String cgxqSql = "select top 24 cm."+wlNameFieldBean.getName()+",cm."+wlCodeFieldBean.getName()+",cm."+sfxjbjFieldBean.getName()+",cs."+cgxqdhFieldBean.getName()+",cs."+xsxmmcFieldBean.getName()+"" +
                ",cs."+ddztFieldBean.getName()+",cs."+xsxmbmFieldBean.getName()+",cs."+xsddbhFieldBean.getName()+",cs."+gisxzlcztFieldBean.getName()+",cs."+htztFieldBean.getName()+" from "+cgxqMasterTableName+" cm left join "+cgxqSubTableName+" cs on cm.Id = cs.formmain_id where cm."+wlCodeFieldBean.getName()+" is not null ORDER BY cm.start_date DESC";
        LOG.info("获取采购需求物料进度-采购需求数据sql："+cgxqSql);
        try {
            List<Map<String, Object>> cgxqDataList = DBKit.excuteSQL(cgxqSql, null);
            for (Map<String, Object> map : cgxqDataList) {
                //获取需求单号
                String cgxqdh = StrKit.str(map.get(cgxqdhFieldBean.getName()));

                //获取销售单号
                String xsddbh = StrKit.str(map.get(xsddbhFieldBean.getName()));

                //获取进度
                ArrayList<String> jdList = new ArrayList<>();
                //获取是否询价状态
                String sfxjbj = StrKit.str(map.get(sfxjbjFieldBean.getName()));
                if(sfxjbj.equals("")){
                    jdList.add("0");
                }else{
                    CtpEnumItem ctpEnumItem = ctpEnumItemCache.getById(Long.parseLong(sfxjbj));
                    if(ctpEnumItem.getShowvalue().equals("否")){
                        jdList.add("0");
                    }
                }
                //获取供应商选择流程状态
                String gisxzlczt = StrKit.str(map.get(gisxzlcztFieldBean.getName()));
                if(!gisxzlczt.equals("")){
                    CtpEnumItem ctpEnumItem = ctpEnumItemCache.getById(Long.parseLong(gisxzlczt));
                    if(ctpEnumItem.getShowvalue().equals("进行中")){
                        jdList.add("1");
                    }
                }

                //获取采购合同编号
                String cghtNum = "";
                //获取供应商合同状态
                String htzt = StrKit.str(map.get(htztFieldBean.getName()));
                if(!htzt.equals("")){
                    CtpEnumItem ctpEnumItem = ctpEnumItemCache.getById(Long.parseLong(htzt));
                    if(ctpEnumItem.getShowvalue().equals("进行中")){
                        jdList.add("2");
                    }else if(!ctpEnumItem.getShowvalue().equals("进行中") && !ctpEnumItem.getShowvalue().equals("未开始")){
                        //获取合同信息数据
                        String htSql = "select "+htbhFieldBean.getName()+" from "+cghtMasterTableName+" cm left join "+cghtSubTableName+" cs on cm.Id = cs.formmain_id where cs."+htXsddbhFieldBean.getName()+" = '"+xsddbh+"'" +
                                " and "+htCgxqdhFieldBean.getName()+" = '"+cgxqdh+"'";
                        List<Map<String, Object>> maps = DBKit.excuteSQL(htSql, null);
                        if(!maps.isEmpty()){
                            cghtNum = StrKit.str(maps.get(0).get(htbhFieldBean.getName()));
                        }
                    }
                }

                String cgOrderNum = "";
                //如果采购需求，供应商选择，采购合同都走完后，才去查询订单档案数据
                if(jdList.isEmpty()){
                    //获取采购订单信息
                    String ddzt = StrKit.str(map.get(ddztFieldBean.getName()));
                    if(!ddzt.equals("")){
                        CtpEnumItem ctpEnumItem = ctpEnumItemCache.getById(Long.parseLong(ddzt));
                        if(ctpEnumItem.getShowvalue().equals("进行中")){
                            jdList.add("3");
                        }else if(!ctpEnumItem.getShowvalue().equals("未开始")){
                            String cgddSql = "select cm."+cgddDjbhFieldBean.getName()+",cs."+rkztFieldBean.getName()+",cs."+fkztFieldBean.getName()+",cs."+spztFieldBean.getName()+" from "+cgdddaMsterTableName+" cm left join "+cgdddaSubTableName+" cs on cm.Id = cs.formmain_id where cs."+cgddXsddbhFieldBean.getName()+" = '"+xsddbh+"'" +
                                    " and "+cgddCgxqsqdhFieldBean.getName()+" = '"+cgxqdh+"'";
                            List<Map<String, Object>> maps = DBKit.excuteSQL(cgddSql, null);
                            if(!maps.isEmpty()){
                                cgOrderNum = StrKit.str(maps.get(0).get(cgddDjbhFieldBean.getName()));
                                //入库状态
                                String rkzt = StrKit.str(maps.get(0).get(rkztFieldBean.getName()));
                                if(!rkzt.equals("")){
                                    CtpEnumItem rkztCtpEnumItem = ctpEnumItemCache.getById(Long.parseLong(rkzt));
                                    if(rkztCtpEnumItem.getShowvalue().equals("进行中")){
                                        jdList.add("4");
                                    }
                                }
                                //付款状态
                                String fkzt = StrKit.str(maps.get(0).get(fkztFieldBean.getName()));
                                if(!fkzt.equals("")){
                                    CtpEnumItem fkztCtpEnumItem = ctpEnumItemCache.getById(Long.parseLong(fkzt));
                                    if(fkztCtpEnumItem.getShowvalue().equals("进行中")){
                                        jdList.add("6");
                                    }
                                }
                                //收票状态
                                String spzt = StrKit.str(maps.get(0).get(spztFieldBean.getName()));
                                if(!spzt.equals("")){
                                    CtpEnumItem spztCtpEnumItem = ctpEnumItemCache.getById(Long.parseLong(spzt));
                                    if(spztCtpEnumItem.getShowvalue().equals("进行中")){
                                        jdList.add("5");
                                    }
                                }
                            }
                        }
                    }
                }

                HashMap<String, Object> cgxqDataMap = new HashMap<>();
                cgxqDataMap.put("itemName",map.get(wlNameFieldBean.getName()));
                cgxqDataMap.put("itemCode",map.get(wlCodeFieldBean.getName()));
                cgxqDataMap.put("xmName",map.get(xsxmmcFieldBean.getName()));
                cgxqDataMap.put("xmCode",map.get(xsxmbmFieldBean.getName()));
                cgxqDataMap.put("xsOrderNum",xsddbh);
                cgxqDataMap.put("cgOrderNum",cgOrderNum);
                cgxqDataMap.put("cghtNum",cghtNum);
                cgxqDataMap.put("schedule",jdList);
                resultMaps.add(cgxqDataMap);
            }
        } catch (Exception e) {
            LOG.info("获取采购需求物料进度-采购需求数据异常：",e);
        }
        return resultMaps;
    }






    /**
     * 获取采购可视化中采购需求
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshCgxqDataInfo(String tempCode){

        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数合同档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //状态-生效
        String lczt_O = "";
        String sfyq_O="";
        try {
            sfyq_O =  FormCap4Kit.getEnumByNameList("Y").get(0).getId().toString();
            lczt_O =  FormCap4Kit.getEnumByNameList("lczt_O").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数合同档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean sfyqFieldBean = formBean.getFieldBeanByDisplay("是否延期");
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("需求状态");
        FormFieldBean createDateFieldBean = formBean.getFieldBeanByDisplay("档案创建日期时间");
        FormFieldBean zxwcDateFieldBean = formBean.getFieldBeanByDisplay("最新完成填报时间-供应商选择流程");
        FormFieldBean suplusQuantity = formBean.getFieldBeanByDisplay("剩余数量");
        FormFieldBean djbh = formBean.getFieldBeanByDisplay("单据编号");
        FormFieldBean sap = formBean.getFieldBeanByDisplay("SAP返回唯一值");
        String tableName = xqztFieldBean.getOwnerTableName();
        //获取总数量
        String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()
                +" from "+tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        String allTimeSql = "select sum(datediff(MINUTE,"+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+")) as alltime, count(1) num from "
                +tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+createDateFieldBean.getName()+" is not null"
                +" and "+zxwcDateFieldBean.getName()+" is not null"
                +" and ("+djbh.getName()+" is not null or "+sap.getName()+" is not null)";
        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //总分
        long fz = 0;
        //有时间数量
        int haveTimeCount=0;
        //平均时间
        String averageDate = "";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            Map<String, Object> allTimeMaps = DBKit.excuteSQL(allTimeSql, null).get(0);
            sumCount = maps.size();
            fz =Integer.parseInt(allTimeMaps.get("alltime").toString());
            haveTimeCount=Integer.parseInt(allTimeMaps.get("num").toString());
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购需求总数异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));

        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+tableName+" where "+suplusQuantity.getName()+" <=0"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购需求已完成数据异常：",e);
        }
        //获取延期数据 
        String yqSql = "select count(1) sumcount from "+tableName+" where "
                +sfyqFieldBean.getName()+" = '"+sfyq_O+"'"
                +" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+zxwcDateFieldBean.getName()+" is null";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yqSql, null);
            postponeCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购需求延期数据异常：",e);
        }
        /*获取未完成数量*/

        String wwcSql = "select count(id) sumcount from "+tableName+" where "+suplusQuantity.getName()+" >0"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购需未已完成数据异常：",e);
        }


        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购总需求"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购已完成需求"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购未完成需求"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化-采购需求已延期"));
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("averageDate",averageDate);
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }

    /**
     * 获取采购可视化中供应商选择
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshGysxzDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数供应商选择档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //状态-生效
        String cgzt_sx = "";
        try {
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数供应商选择档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("供应商选择状态");
        FormFieldBean createDateFieldBean = formBean.getFieldBeanByDisplay("填报日期时间");
        FormFieldBean zxwcDateFieldBean = formBean.getFieldBeanByDisplay("审批结束日期时间");
        String tableName = xqztFieldBean.getOwnerTableName();
        //获取总数量
        String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "+tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //有时间数量
        int haveTimeCount=0;
        //总分
        long fz = 0;
        //平均时间
        String averageDate = "";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            sumCount = maps.size();
            for (Map<String, Object> map : maps) {
                String createDateStr = StrKit.str(map.get(createDateFieldBean.getName()));
                String zxwcDateStr = StrKit.str(map.get(zxwcDateFieldBean.getName()));
                if(!createDateStr.equals("") && !zxwcDateStr.equals("")){
                    //计算两个时间相差分
                    haveTimeCount++;
                    fz += DateUtil.getTime(createDateStr, zxwcDateStr);
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取供应商选择总数异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));
        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+tableName+" where "+xqztFieldBean.getName()+" = '"+cgzt_sx+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取供应商选择已完成数据异常：",e);
        }

        noAccomplishCount = sumCount - accomplishCount;

        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("供应商选择总数"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("供应商选择已完成数"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("供应商选择未完成数"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("供应商选择已取消数"));
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("averageDate",averageDate);
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }


    /**
     * 获取采购可视化中采购合同
     * @param tempCode
     * @return
     */
    public Map<String,Object> getCgkshCghtDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购合同档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //状态-生效
        String cgzt_sx = "";
        String cgzt_qx = "";
        String cgzt_ywc = "";
        String sfyq_s="";
        String htdl_xnht="";
        //合同履约状态枚举 已完成 未完成
        String htlxzt_wlda_yjs="";
        String htlxzt_wlda_wjs="";
        try {
            sfyq_s =  FormCap4Kit.getEnumByNameList("Y").get(0).getId().toString();
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
            cgzt_qx =  FormCap4Kit.getEnumByNameList("cgzt_qx").get(0).getId().toString();
            cgzt_ywc =  FormCap4Kit.getEnumByNameList("cgzt_ywc").get(0).getId().toString();
            htlxzt_wlda_yjs =  FormCap4Kit.getEnumByNameList("htlxzt_wlda_yjs").get(0).getId().toString();
            htlxzt_wlda_wjs =  FormCap4Kit.getEnumByNameList("htlxzt_wlda_wjs").get(0).getId().toString();
            htdl_xnht =  FormCap4Kit.getEnumByNameList("xnht").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数采购合同档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean htdlFieldBean = formBean.getFieldBeanByDisplay("合同大类");
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("合同状态");
        FormFieldBean sfyqFieldBean = formBean.getFieldBeanByDisplay("是否延期");
        FormFieldBean createDateFieldBean = formBean.getFieldBeanByDisplay("填报时间日期");
        FormFieldBean zxwcDateFieldBean = formBean.getFieldBeanByDisplay("审批结束日期时间");
        FormFieldBean performanceContractStatu = formBean.getFieldBeanByDisplay("合同履约状态");
        String tableName = xqztFieldBean.getOwnerTableName();
        //获取总数量
        String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "
                +tableName +" where "+xqztFieldBean.getName() +" != '"+cgzt_qx+"'"
                +" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";

        String allTimeSql = "select sum(datediff(MINUTE,"+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+"))  alltime, count(1) num from "
                +tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+createDateFieldBean.getName()+" is not null"
                +" and "+zxwcDateFieldBean.getName()+" is not null"
                +" and "+xqztFieldBean.getName() +" != '"+cgzt_qx+"'"
                +" and "+htdlFieldBean.getName()+" != '"+htdl_xnht+"'";

        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //取消数量
        int qxCount = 0;
        //已延期数量
        int postponeCount = 0;
        //总分
        long fz = 0;
        //有时间数量
        int haveTimeCount=0;
        //平均时间
        String averageDate = "";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            Map<String, Object> allTimeMaps = DBKit.excuteSQL(allTimeSql, null).get(0);
            sumCount = maps.size();
            fz =Integer.parseInt(allTimeMaps.get("alltime").toString());
            haveTimeCount=Integer.parseInt(allTimeMaps.get("num").toString());
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购合同总数异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));

        //取消数量
        String sqlQx = "select count(id) qxcount from "+tableName+" where "+xqztFieldBean.getName() +" = '"+cgzt_qx+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sqlQx, null);
            qxCount = Integer.parseInt(StrKit.str(maps.get(0).get("qxcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购合同取消异常：",e);
        }
        //获取已完成数据
        String ywcSql = "select count(id) qxcount from "+tableName+" where ("+xqztFieldBean.getName()+" != '"+cgzt_qx+"'"+" or "+xqztFieldBean.getName()+" is  null)"
                +" and "+performanceContractStatu.getName()+" = "+htlxzt_wlda_yjs +" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("qxcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购合同已完成数据异常：",e);
        }
        //获取延期数据
        String yqSql = "select count(id) qxcount from "+tableName+" where "
                +sfyqFieldBean.getName()+" = '"+sfyq_s+"'"+" and "
                +createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+zxwcDateFieldBean.getName()+" is null";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yqSql, null);
            postponeCount = Integer.parseInt(StrKit.str(maps.get(0).get("qxcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购合同延期数据异常：",e);
        }
        //获取未完成数量
        String wwcSql = "select count(id) qxcount from "+tableName+" where ("+xqztFieldBean.getName()+" != '"+cgzt_qx+"'"+" or "+xqztFieldBean.getName()+" is null)"+" and "+performanceContractStatu.getName()+" = "+htlxzt_wlda_wjs+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("qxcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购合同未完成数据异常：",e);
        }


        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购合同总数查询"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购合同已完成查询"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购合同未完成查询"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化-采购合同已延期"));
        resultMap.put("cancelCountCount",qxCount);
        resultMap.put("cancelCountUrl",FormCap4Kit.getBbAddres("采购合同已取消查询"));
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("averageDate",averageDate);
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }


    /**
     * 获取采购可视化中采购付款
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshCgFkDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购订单档案表数据信息异常：",e);
            return resultMap;
        }
        //获取付款档案表信息
        FormBean fkformBean = null;
        try {
            fkformBean = cap4FormManager.getFormByFormCode(cgfkDATempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购付款档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //状态-生效
        String cgzt_sx = "";
        String cgzt_dj = "";
        String cgzt_ywc = "";
        try {
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
            cgzt_dj =  FormCap4Kit.getEnumByNameList("cgzt_dj").get(0).getId().toString();
            cgzt_ywc =  FormCap4Kit.getEnumByNameList("cgzt_ywc").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数采购订单档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("采购订单状态");
        FormFieldBean createDateFieldBean = fkformBean.getFieldBeanByDisplay("填报日期时间");
        FormFieldBean zxwcDateFieldBean = fkformBean.getFieldBeanByDisplay("审批日期时间");
        //付款里程碑明细表字段
        FormFieldBean fkztlcbFieldBean = formBean.getFieldBeanByDisplay("付款状态-付款里程碑");
        FormFieldBean yjfkdateFieldBean = formBean.getFieldBeanByDisplay("预计付款时间-付款里程碑");
        String tableName = xqztFieldBean.getOwnerTableName();
        String subTableName = fkztlcbFieldBean.getOwnerTableName();
        //采购付款档案表表名
        String cgfkTableName = createDateFieldBean.getOwnerTableName();

        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //平均时间
        String averageDate = "";
        //总分
        long fz = 0;
        //有时间数量
        int haveTimeCount=0;
        int fkCount = 0;
        //获取总分钟
        try {
            //获取总数量
            String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "+cgfkTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> dataMaps = DBKit.excuteSQL(sql, null);
            fkCount = dataMaps.size();
            for (Map<String, Object> dataMap : dataMaps) {
                String createDateStr = StrKit.str(dataMap.get(createDateFieldBean.getName()));
                String zxwcDateStr = StrKit.str(dataMap.get(zxwcDateFieldBean.getName()));
                if(!createDateStr.equals("") && !zxwcDateStr.equals("")) {
                    haveTimeCount++;
                    //计算两个时间相差分
                    fz += DateUtil.getTime(createDateStr, zxwcDateStr);
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购付款总分总异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));

        try {
            //获取总数量
            String sql = "select count(id) sumcount from "+subTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            sumCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购付款总数异常：",e);
        }
        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+subTableName+"  where "+fkztlcbFieldBean.getName()+" = '"+cgzt_ywc+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单已完成数据异常：",e);
        }

        //获取未完成状态数据
        String wwcSql = "select count(id) sumcount from "+subTableName+"  where "+fkztlcbFieldBean.getName()+" = null"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单未完成数据异常：",e);
        }

        //获取已延期数据
        String yyqSql = "select s."+yjfkdateFieldBean.getName()+" from "+tableName+" f, "+subTableName+" s where f."
                +xqztFieldBean.getName()+" in('"+cgzt_sx+"','"+cgzt_dj+"') and s."+fkztlcbFieldBean.getName()+" = null"
                +" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yyqSql, null);
            for (Map<String, Object> map : maps) {
                String str = StrKit.str(map.get(yjfkdateFieldBean.getName()));
                if(!str.equals("")){
                    //判断当前时间是否大于预计付款时间
                    Boolean dataGreaterThanAndLessThan = DateUtil.getDataGreaterThan(str);
                    if(dataGreaterThanAndLessThan){
                        postponeCount++;
                    }
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单已延期数据异常：",e);
        }

        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购付款查询"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购付款已完成查询"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购付款未完成查询"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化采购付款已延期"));
        resultMap.put("cancelCountCount",0);
        resultMap.put("cancelCountUrl",FormCap4Kit.getBbAddres("采购付款订单已取消查询"));
        resultMap.put("averageDate",averageDate);
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }



    /**
     * 获取采购可视化中采购订单
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshCgDDDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购订单表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //状态-生效
        String cgzt_sx = "";
        String cgzt_dj = "";
        String cgzt_ywc = "";
        String cgzt_qx = "";
        String sfyq_s="";
        /*已结束 未结束*/
        String htlxzt_wlda_yjs="";


        try {
            sfyq_s=FormCap4Kit.getEnumByNameList("Y").get(0).getId().toString();
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
            cgzt_dj =  FormCap4Kit.getEnumByNameList("cgzt_dj").get(0).getId().toString();
            cgzt_ywc =  FormCap4Kit.getEnumByNameList("cgzt_ywc").get(0).getId().toString();
            cgzt_qx =  FormCap4Kit.getEnumByNameList("cgzt_qx").get(0).getId().toString();
            htlxzt_wlda_yjs =  FormCap4Kit.getEnumByNameList("htlxzt_wlda_yjs").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数采购订单档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("采购订单状态");
        FormFieldBean ddwcztFieldBean = formBean.getFieldBeanByDisplay("订单完成状态");
        FormFieldBean createDateFieldBean = formBean.getFieldBeanByDisplay("填报时间日期");
        FormFieldBean zxwcDateFieldBean = formBean.getFieldBeanByDisplay("审批时间日期");
        FormFieldBean sfyqFieldBean = formBean.getFieldBeanByDisplay("是否延期");
        String tableName = xqztFieldBean.getOwnerTableName();
        //获取总数量
        String sql = "select id,"+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "+tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        String allTimeSql = "select sum(datediff(MINUTE,"+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+")) as alltime, count(1) num from "
                +tableName+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+createDateFieldBean.getName()+" is not null"
                +" and "+zxwcDateFieldBean.getName()+" is not null";
        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //平均处理时间
        String averageDate = "";
        //获取取消数量
        int cancelCount = 0;
        //有时间数量
        int haveTimeCount=0;
        //总分
        long fz = 0;
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            Map<String, Object> allTimeMaps = DBKit.excuteSQL(allTimeSql, null).get(0);
            sumCount = maps.size();
            fz = Integer.parseInt(allTimeMaps.get("alltime").toString());
            haveTimeCount=Integer.parseInt(allTimeMaps.get("num").toString());
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单总分钟异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));
        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+tableName+" where "+ddwcztFieldBean.getName()+" = '"+htlxzt_wlda_yjs+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单已完成数据异常：",e);
        }

        //获取未完成数据
        String wwcSql = "select count(id) sumcount from "+tableName+" where "+ddwcztFieldBean.getName()+" != '"+htlxzt_wlda_yjs+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单未完成数据异常：",e);
        }
        //获取取消数据
        String qxcSql = "select count(id) sumcount from "+tableName+" where "+xqztFieldBean.getName()+" = '"+cgzt_qx+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(qxcSql, null);
            cancelCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单取消数据异常：",e);
        }
        //获取延期数据
        String yqSql = "select count(id) sumcount from "+tableName+" where "
                +sfyqFieldBean.getName()+" = '"+sfyq_s+"'"
                +" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'"
                +" and "+zxwcDateFieldBean.getName()+" is null";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yqSql, null);
            postponeCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单延期数据异常：",e);
        }
//        noAccomplishCount = sumCount - accomplishCount;
        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购金额统计表-总数查询"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购金额统计表-已完成查询"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购金额统计表-未完成查询"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化-采购订单已延期"));
        resultMap.put("cancelCount",cancelCount);
        resultMap.put("cancelCountUrl",FormCap4Kit.getBbAddres("采购金额统计表-已取消查询"));
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("averageDate",averageDate);
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }

    /**
     * 获取采购可视化中采购入库
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshCgrkDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购订单档案表数据信息异常：",e);
            return resultMap;
        }
        //获取采购入库档案表信息
        FormBean fkformBean = null;
        try {
            fkformBean = cap4FormManager.getFormByFormCode(cgrkDATempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购入库档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //流程状态-已完成
        String lczt_O = "";
        String cgzt_sx = "";
        String cgzt_dj = "";
        try {
            lczt_O =  FormCap4Kit.getEnumByNameList("lczt_O").get(0).getId().toString();
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
            cgzt_dj =  FormCap4Kit.getEnumByNameList("cgzt_dj").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数采购订单档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("采购订单状态");
        //采购入库
        FormFieldBean createDateFieldBean = fkformBean.getFieldBeanByDisplay("填报时间日期");
        FormFieldBean zxwcDateFieldBean = fkformBean.getFieldBeanByDisplay("审批结束时间日期");
        //物料明细表字段
        FormFieldBean fkztlcbFieldBean = formBean.getFieldBeanByDisplay("入库状态-物料明细");
        //交货里程碑明细表
        FormFieldBean jhrqdateFieldBean = formBean.getFieldBeanByDisplay("交货日期-交货计划");
        String tableName = xqztFieldBean.getOwnerTableName();
        String subTableName = fkztlcbFieldBean.getOwnerTableName();
        String jhlcbTableName = jhrqdateFieldBean.getOwnerTableName();
        //采购入库档案表表名
        String cgfkTableName = createDateFieldBean.getOwnerTableName();

        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //平均时间
        String averageDate = "";
        //总分
        long fz = 0;
        //有时间数量
        int haveTimeCount=0;
        int fkCount = 0;
        //获取总分钟
        try {
            //获取总数量
            String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "+cgfkTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> dataMaps = DBKit.excuteSQL(sql, null);
            fkCount = dataMaps.size();
            for (Map<String, Object> dataMap : dataMaps) {
                String createDateStr = StrKit.str(dataMap.get(createDateFieldBean.getName()));
                String zxwcDateStr = StrKit.str(dataMap.get(zxwcDateFieldBean.getName()));
                //计算两个时间相差分
                if (!"".equals(createDateStr) && !"".equals(zxwcDateStr)) {
                    haveTimeCount++;
                    fz += DateUtil.getTime(createDateStr, zxwcDateStr);
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购入库总分钟异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));

        try {
            //获取总数量
            String sql = "select count(id) sumcount from "+subTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            sumCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购入库总数异常：",e);
        }
        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+subTableName+"  where "+fkztlcbFieldBean.getName()+" = '"+lczt_O+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购入库已完成数据异常：",e);
        }

        //获取未完成状态数据
        String wwcSql = "select count(1) sumcount from "+tableName+" f,"+subTableName+" s  where f."+xqztFieldBean.getName()+" in('"+cgzt_sx+"','"+cgzt_dj+"') and "+fkztlcbFieldBean.getName()+" = null"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购入库未完成数据异常：",e);
        }

        //获取已延期数据
        String yyqSql = "select j."+jhrqdateFieldBean.getName()+" from "+tableName+" f left join "+subTableName+" s on f.Id = s.formmain_id left join "+jhlcbTableName+" j on f.Id = j.formmain_id where f."+xqztFieldBean.getName()+" in('"+cgzt_sx+"','"+cgzt_dj+"') and s."+fkztlcbFieldBean.getName()+" = null"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yyqSql, null);
            for (Map<String, Object> map : maps) {
                String str = StrKit.str(map.get(jhrqdateFieldBean.getName()));
                if(!str.equals("")){
                    //判断当前时间是否大于预计付款时间
                    Boolean dataGreaterThanAndLessThan = DateUtil.getDataGreaterThan(str);
                    if(dataGreaterThanAndLessThan){
                        postponeCount++;
                    }
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取采购订单已延期数据异常：",e);
        }

        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购订单入库总数查询"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购订单入库已完成查询"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购订单入库未完成查询"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化采购付款已延期"));
        resultMap.put("cancelCountCount",0);
        resultMap.put("cancelCountUrl",FormCap4Kit.getBbAddres("采购可视化-采购付款取消"));
        resultMap.put("averageDate",averageDate);
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }


    /**
     * 获取采购可视化中收票登记
     * @param tempCode
     * @return
     */
    private Map<String,Object> getCgkshSpdjDataInfo(String tempCode){
        HashMap<String, Object> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购订单档案表数据信息异常：",e);
            return resultMap;
        }
        //获取收票登记档案表信息
        FormBean fkformBean = null;
        try {
            fkformBean = cap4FormManager.getFormByFormCode(spdjDATempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数采购入库档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //流程状态-已完成
        String lczt_O = "";
        String cgzt_sx = "";
        String cgzt_dj = "";
        try {
            lczt_O =  FormCap4Kit.getEnumByNameList("lczt_O").get(0).getId().toString();
            cgzt_sx =  FormCap4Kit.getEnumByNameList("cgzt_sx").get(0).getId().toString();
            cgzt_dj =  FormCap4Kit.getEnumByNameList("cgzt_dj").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数采购订单档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean xqztFieldBean = formBean.getFieldBeanByDisplay("采购订单状态");
        //采购入库
        FormFieldBean createDateFieldBean = fkformBean.getFieldBeanByDisplay("填报时间日期");
        FormFieldBean zxwcDateFieldBean = fkformBean.getFieldBeanByDisplay("审批结束时间日期");
        //物料明细表字段
        FormFieldBean fkztlcbFieldBean = formBean.getFieldBeanByDisplay("付款状态-物料明细");
        FormFieldBean spztFieldBean = formBean.getFieldBeanByDisplay("收票状态-物料明细");
        //付款程碑明细表字段
        FormFieldBean yjfkdateFieldBean = formBean.getFieldBeanByDisplay("预计付款时间-付款里程碑");
        String tableName = xqztFieldBean.getOwnerTableName();
        //物料明细表
        String subTableName = fkztlcbFieldBean.getOwnerTableName();
        //付款里程碑明细表名
        String jhlcbTableName = yjfkdateFieldBean.getOwnerTableName();

        //收票登记档案表表名
        String cgfkTableName = createDateFieldBean.getOwnerTableName();

        //总数量
        int sumCount = 0;
        //已完成数量
        int accomplishCount = 0;
        //未完成数量
        int noAccomplishCount = 0;
        //已延期数量
        int postponeCount = 0;
        //平均时间
        String averageDate = "";
        //总分
        long fz = 0;
        //有时间数量
        int haveTimeCount=0;
        int fkCount = 0;
        //获取总分钟
        try {
            //获取总数量
            String sql = "select "+createDateFieldBean.getName()+","+zxwcDateFieldBean.getName()+" from "+cgfkTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> dataMaps = DBKit.excuteSQL(sql, null);
            fkCount = dataMaps.size();
            for (Map<String, Object> dataMap : dataMaps) {
                String createDateStr = StrKit.str(dataMap.get(createDateFieldBean.getName()));
                String zxwcDateStr = StrKit.str(dataMap.get(zxwcDateFieldBean.getName()));
                if (!"".equals(createDateStr) && !"".equals(zxwcDateStr)) {
                    haveTimeCount++;
                    //计算两个时间相差分
                    fz += DateUtil.getTime(createDateStr, zxwcDateStr);
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取收票登记总分钟异常：",e);
        }
        if (haveTimeCount==0){
            haveTimeCount=1;
        }
        averageDate = DateUtil.minutesToHour((int)Math.ceil((float) fz / haveTimeCount));

        try {
            //获取总数量
            String sql = "select count(id) sumcount from "+subTableName+""+" where "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            sumCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取收票登记总数异常：",e);
        }
        //获取已完成数据
        String ywcSql = "select count(id) sumcount from "+subTableName+"  where "+fkztlcbFieldBean.getName()+" = '"+lczt_O+"'"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(ywcSql, null);
            accomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取收票登记已完成数据异常：",e);
        }

        //获取未完成状态数据
        String wwcSql = "select count(id) sumcount from "+subTableName+" where "+spztFieldBean.getName()+" = null"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(wwcSql, null);
            noAccomplishCount = Integer.parseInt(StrKit.str(maps.get(0).get("sumcount")));
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取收票登记未完成数据异常：",e);
        }

        //获取已延期数据
        String yyqSql = "select j."+yjfkdateFieldBean.getName()+" from "+tableName+" f left join "+subTableName+" s on f.Id = s.formmain_id left join "+jhlcbTableName+" j on f.Id = j.formmain_id where f."+xqztFieldBean.getName()+" in('"+cgzt_sx+"','"+cgzt_dj+"') and s."+spztFieldBean.getName()+" = null"+" and "+createDateFieldBean.getName()+" > '2025-3-5 15:30:00'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(yyqSql, null);
            for (Map<String, Object> map : maps) {
                String str = StrKit.str(map.get(yjfkdateFieldBean.getName()));
                if(!str.equals("")){
                    //判断当前时间是否大于预计付款时间
                    Boolean dataGreaterThanAndLessThan = DateUtil.getDataGreaterThan(str);
                    if(dataGreaterThanAndLessThan){
                        postponeCount++;
                    }
                }
            }
        } catch (Exception e) {
            LOG.info("SRM看板-采购可视化获取收票登记已延期数据异常：",e);
        }

        resultMap.put("sumCount",sumCount);
        resultMap.put("sumCountUrl",FormCap4Kit.getBbAddres("采购订单收票总数"));
        resultMap.put("accomplishCount",accomplishCount);
        resultMap.put("accomplishCountUrl",FormCap4Kit.getBbAddres("采购订单已收票总数"));
        resultMap.put("noAccomplishCount",noAccomplishCount);
        resultMap.put("noAccomplishCountUrl",FormCap4Kit.getBbAddres("采购订单未收票总数"));
        resultMap.put("postponeCount",postponeCount);
        resultMap.put("postponeCountUrl",FormCap4Kit.getBbAddres("采购可视化采购付款已延期"));
        resultMap.put("cancelCountCount",0);
        resultMap.put("cancelCountUrl",FormCap4Kit.getBbAddres("采购可视化-采购付款取消"));
        resultMap.put("averageDate",averageDate);
        resultMap.put("countMinute",(long)Math.ceil((float) fz / haveTimeCount));
        resultMap.put("haveTimeCount",haveTimeCount);  
        resultMap.put("allTimeMintue",fz);
        return resultMap;
    }




    //获取收票登记数据
    private Map<String, List<Map<String, Object>>> getSpFlowStatementInfo(String tempCode){
        Map<String, List<Map<String, Object>>> resultMap = new HashMap<>();
        TemplateManager templateManager = (TemplateManager) AppContext.getBean("templateManager");
        CtpTemplate ctpTemplate = templateManager.getTempleteByTemplateNumber(tempCode);
        //获取表单数据
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //流程状态-进行中
        String splx_sp = "";
        try {
            splx_sp =  FormCap4Kit.getEnumByNameList("splx_sp").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数收票登记流程表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean tbrFieldBean = formBean.getFieldBeanByDisplay("填报人");
        FormFieldBean splxFieldBean = formBean.getFieldBeanByDisplay("收票类型");
        String ownerTableName = tbrFieldBean.getOwnerTableName();
        String sql = "select om.NAME name from col_summary ct left join "+ownerTableName+" f on ct.FORM_RECORDID = f.ID left join org_member om ON om.ID = f."+tbrFieldBean.getName()+" where ct.TEMPLETE_ID = '"+ctpTemplate.getId()+"' and ct.STATE= 0 and f."+splxFieldBean.getName()+" = '"+splx_sp+"'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            if(!maps.isEmpty()){
                resultMap = maps.stream().collect(Collectors.groupingBy(m -> m.get("name").toString()));
            }
        } catch (Exception e) {
            LOG.info("SRM看板-根据模板编码获取流程数据异常：",e);
            return resultMap;
        }
        return resultMap;
    }





    //获取看板取数逻辑
    private Map<String, List<Map<String, Object>>> getKbqsHtDataInfo(String tempCode){
        Map<String, List<Map<String, Object>>> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取看板取数合同档案表数据信息异常：",e);
            return resultMap;
        }

        //获取枚举信息
        //流程状态-进行中
        String lczt_U = "";
        try {
            lczt_U =  FormCap4Kit.getEnumByNameList("lczt_U").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取看板取数合同档案表数据转换枚举异常：",e);
            return resultMap;
        }
        FormFieldBean bdztFieldBean = formBean.getFieldBeanByDisplay("表单状态");
        FormFieldBean tbrFieldBean = formBean.getFieldBeanByDisplay("填报人");
        String tableName = bdztFieldBean.getOwnerTableName();
        String sql = "select om.NAME name from "+tableName+" f left join org_member om ON om.ID = f."+tbrFieldBean.getName()+" where f."+bdztFieldBean.getName()+" = '"+lczt_U+"'";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            if(!maps.isEmpty()){
                resultMap = maps.stream().collect(Collectors.groupingBy(m -> m.get("name").toString()));
            }
        } catch (Exception e) {
            LOG.info("SRM看板-获取采购需求数据异常：",e);
            return resultMap;
        }
        return resultMap;
    }



    /**
     * 获取采购需求数据
     * @param tempCode
     * @return
     */
    private Map<String, List<Map<String, Object>>> getCgxqDataInfo(String tempCode){
        Map<String, List<Map<String, Object>>> resultMap = new HashMap<>();
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取采购需求档案表数据信息异常：",e);
            return resultMap;
        }
        //获取枚举信息
        //流程状态-进行中
        String lczt_U = "";
        //流程状态-未开始
        String lczt_N = "";
        try {
            lczt_U =  FormCap4Kit.getEnumByNameList("lczt_U").get(0).getId().toString();
            lczt_N =  FormCap4Kit.getEnumByNameList("lczt_N").get(0).getId().toString();
        }catch (Exception e){
            LOG.info("SRM看板-获取采购需求数据转换枚举异常：",e);
            return resultMap;
        }

        FormFieldBean xqrFieldBean = formBean.getFieldBeanByDisplay("需求人");
        FormFieldBean bjztFieldBean = formBean.getFieldBeanByDisplay("供应商选择流程状态");
        String tableName = xqrFieldBean.getOwnerTableName();

        String sql = "select om.NAME name from "+tableName+" f left join org_member om ON om.ID = f."+xqrFieldBean.getName()+" where f."+bjztFieldBean.getName()+" in('"+lczt_U+"','"+lczt_N+"',null)";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            if(!maps.isEmpty()){
                resultMap = maps.stream().collect(Collectors.groupingBy(m -> m.get("name").toString()));
            }
        } catch (Exception e) {
            LOG.info("SRM看板-获取采购需求数据异常：",e);
            return resultMap;
        }

        return resultMap;
    }







    /**
     * 获取流程表数据
     * @param tempCode 流程表编码
     */
    private Map<String, List<Map<String, Object>>> getFlowStatementInfo(String tempCode){
        Map<String, List<Map<String, Object>>> resultMap = new HashMap<>();
        TemplateManager templateManager = (TemplateManager) AppContext.getBean("templateManager");
        CtpTemplate ctpTemplate = templateManager.getTempleteByTemplateNumber(tempCode);
        //获取表单数据
        FormBean formBean = null;
        try {
            formBean = cap4FormManager.getFormByFormCode(tempCode);
        } catch (BusinessException e) {
            LOG.info("SRM看板-根据模板编码获取表数据信息异常：",e);
            return resultMap;
        }
        FormFieldBean tbrFieldBean = formBean.getFieldBeanByDisplay("填报人");
        String ownerTableName = tbrFieldBean.getOwnerTableName();
        String sql = "select om.NAME name from col_summary ct left join "+ownerTableName+" f on ct.FORM_RECORDID = f.ID left join org_member om ON om.ID = f."+tbrFieldBean.getName()+" where ct.TEMPLETE_ID = '"+ctpTemplate.getId()+"' and ct.STATE= 0";
        try {
            List<Map<String, Object>> maps = DBKit.excuteSQL(sql, null);
            if(!maps.isEmpty()){
                resultMap = maps.stream().collect(Collectors.groupingBy(m -> m.get("name").toString()));
            }
        } catch (Exception e) {
            LOG.info("SRM看板-根据模板编码获取流程数据异常：",e);
            return resultMap;
        }
        return resultMap;
    }


    /**
     * 排除采购订单中取消状态的档案
     * @param code
     * @return
     * @throws BusinessException
     * @throws SQLException
     */
    public Map<Object,Object> selectNotQxList(String code) throws BusinessException, SQLException {
        List<Long> idsList=new ArrayList<>();
        Map<Object,Object> map=new HashMap<>();
        String purchase_status = AppContext.getSystemProperty("brwh.purchase_status");
        String purchase_status_qx = AppContext.getSystemProperty("brwh.purchase_status_qx");
        String cgdddaCggcs = AppContext.getSystemProperty("brwh.cgdddaCggcs");
        CtpEnumItem qxEnum = FormCap4Kit.getEnumByCode(purchase_status_qx);
        //获取对应表单
        FormBean formBean = cap4FormManager.getFormByFormCode(code);
        List<FormFieldBean> formFieldBeans = formBean.getMasterTableBean().getFields();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, ");
        String purchasestatusFieldName = "";
        String cgdddaCggcsFieldName="";
        for (FormFieldBean formFieldBean : formFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (purchase_status.equals(fieldDesc)) {
                purchasestatusFieldName = formFieldBean.getName();
            }
            if (cgdddaCggcs.equals(fieldDesc)){
                cgdddaCggcsFieldName = formFieldBean.getName();
            }
            if (!"".equals(fieldDesc)) {
                String fieldName = formFieldBean.getName();
                stb.append(fieldName).append(",");
            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        String tableName = formBean.getMasterTableBean().getTableName();
        String newStr = str + " from " + tableName + " where " + purchasestatusFieldName + " != " + qxEnum.getId();
        LOG.info("排除采购订单中取消状态的档案SQL:"+newStr);
        /* 执行SQL查询 */
        List dataList = capDataListSqlHelpManager.selectDataList(newStr, null);
        if (!dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> dataMap = (Map<String, Object>) dataList.get(i);
                long num = Long.parseLong(dataMap.get("id").toString());
                if (dataMap.get(cgdddaCggcsFieldName)!=null){
                    if (dataMap.get(cgdddaCggcsFieldName)!=null){
                        V3xOrgMember member = orgManager.getMemberById(Long.parseLong(dataMap.get(cgdddaCggcsFieldName).toString()));
                        map.put(num,member.getName());
                        idsList.add(num);
                    }else{
                        LOG.info("采购工程师id为空，故跳过");
                        continue;
                    }
                }
            }
        }
        map.put("ids",idsList);
        LOG.info("采购订单档案:"+JSON.toJSONString(map));
        return map;
    }

    /**
     * 将本率
     * @return
     */
    public Map<String, Object> getJblData() throws BusinessException, SQLException {
        /* 从系统属性中获取配置信息 */
        //采购订单档案
        String cgdddaFormCode = AppContext.getSystemProperty("brwh.cgdddaFormCode");
        Map<Object, Object> selectMainList = selectNotQxList(cgdddaFormCode);
        List<Object> idsListObject = (List<Object>) selectMainList.get("ids");
        String commaSeparatedString = idsListObject.stream()
                .map(Object::toString)
                .collect(Collectors.joining(","));
        //明细表降本总额
        String cgdddasubjbze = AppContext.getSystemProperty("brwh.cgdddasubjbze");
        //明细表基准总额
        String cgdddasubjzze = AppContext.getSystemProperty("brwh.cgdddasubjzze");
        //物料名称
        String scwlmx = AppContext.getSystemProperty("brwh.scwlmx");

        //获取对应表单
        FormBean formBean = cap4FormManager.getFormByFormCode(cgdddaFormCode);
        List<FormFieldBean> allFieldBeans = formBean.getAllFieldBeans();
        StringBuilder stb = new StringBuilder();
        stb.append("select id, formmain_id,");
        String cgdddasubjbzeFieldName = "";
        String cgdddasubjzzeFieldName="";
        for (FormFieldBean formFieldBean : allFieldBeans) {
            String fieldDesc = StrKit.str(formFieldBean.getDesc().trim());
            if (formFieldBean.isSubField()) {
                //降本总额
                if (cgdddasubjbze.equals(fieldDesc)) {
                    cgdddasubjbzeFieldName = formFieldBean.getName();
                }
                if (cgdddasubjzze.equals(fieldDesc)){
                    cgdddasubjzzeFieldName = formFieldBean.getName();
                }
                if (!"".equals(fieldDesc)) {
                    String fieldName = formFieldBean.getName();
                    stb.append(fieldName).append(",");
                }

            }
        }
        /* 删除最后一个逗号，完成SQL查询语句的构建 */
        String str = stb.substring(0, stb.length() - 1);
        List<FormTableBean> subTableBean = formBean.getSubTableBean();
        StringBuilder newStr=new StringBuilder();
        for (int i = 0; i < subTableBean.size(); i++) {
            String tableName = "";
            if (scwlmx.equals(subTableBean.get(i).getDisplay())) {
                tableName = subTableBean.get(i).getTableName();
                if (!tableName.isEmpty()) {
                    newStr .append(str + " from " + tableName);
                    if (!commaSeparatedString.isEmpty()){
                        newStr .append(" where formmain_id in ("+commaSeparatedString+")");;
                    }
                    LOG.info("采购订单明细表-----sql语句：" + newStr);
                }
            }
        }
        Set<String> nameSet = new HashSet<>();
        Map<String,Object> resMsgMap=new HashMap<>();
        /* 执行SQL查询 */
        List<Map<String, Object>> dataList =
                capDataListSqlHelpManager.selectDataList(newStr.toString(), null);
        LOG.info("采购订单档案信息-------" + JSON.toJSONString(dataList));
        if (!dataList.isEmpty()) {
            for (Map<String, Object> stringObjectMap : dataList) {
                if (stringObjectMap.get("formmain_id") != null
                        &&stringObjectMap.get(cgdddasubjbzeFieldName)!=null
                        &&stringObjectMap.get(cgdddasubjzzeFieldName)!=null) {
                    if (selectMainList.get(stringObjectMap.get("formmain_id"))==null){
                        continue;
                    }
                    //采购工程师
                    String name = selectMainList.get(stringObjectMap.get("formmain_id")).toString();
                    //降本总额
                    BigDecimal jbzeValue = (BigDecimal) stringObjectMap.get(cgdddasubjbzeFieldName);
                    double jbze = jbzeValue.doubleValue();
                    //基准总额
                    BigDecimal jzzeValue = (BigDecimal) stringObjectMap.get(cgdddasubjzzeFieldName);
                    double jzze = jzzeValue.doubleValue();
                    if (resMsgMap.get("jbze_"+name)!=null&&resMsgMap.get("jzze_"+name)!=null){
                        double  jbze_res = (double)resMsgMap.get("jbze_"+name);
                        double  jzze_res = (double)resMsgMap.get("jzze_"+name);
                        resMsgMap.put("jbze_"+name,jbze_res+jbze);
                        resMsgMap.put("jzze_"+name,jzze_res+jzze);
                        nameSet.add(name);
                    }else{
                        resMsgMap.put("jbze_"+name, jbze);
                        resMsgMap.put("jzze_"+name, jzze);
                        nameSet.add(name);
                    }
                }else{
                    continue;
                }
            }
        }
        Map<String,Object> resMap=new HashMap<>();
        for (String name:nameSet){
            double  jbze_res = (double)  resMsgMap.get("jbze_"+name);
            double  jzze_res = (double) resMsgMap.get("jzze_"+name);
            double result = (jbze_res / jzze_res) * 100;
            BigDecimal bd = new BigDecimal(Double.toString(result));
            BigDecimal roundedResult = bd.setScale(2, RoundingMode.HALF_UP);
            resMap.put(name,roundedResult.doubleValue());
        }
        return resMap;
    }

}
